/****** Object:  StoredProcedure [dbo].[AuthZ_spGetRolesByGroup]    Script Date: 06/24/2009 23:45:54 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[AuthZ_spGetRolesByGroup]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[AuthZ_spGetRolesByGroup]
GO

/****** Object:  StoredProcedure [dbo].[AuthZ_spGetRolesByGroup]    Script Date: 06/24/2009 23:45:54 ******/
SET ANSI_NULLS ON
GO

-- =============================================
-- Author:		<revans>
-- Create date: <4-13-2009>
-- Description:	<Retrieves all Groups and their related Roles.>
-- Param:
-- =============================================
CREATE PROCEDURE [dbo].[AuthZ_spGetRolesByGroup]

	@groupFilter as nvarchar(max) = ''

AS
BEGIN
	BEGIN TRY
		SET NOCOUNT ON;
		
		CREATE TABLE [dbo].[#TopRoles] (GroupID int, RoleID int, ParentRoleID int)
		IF @groupFilter = '' or @groupFilter is null
		BEGIN
			INSERT INTO [dbo].[#TopRoles]

			/* Get the mapping of groups to all top level roles including those with no children. */
			SELECT  ag.GroupID
			, r.RoleID
			, r.ParentRoleID
			FROM RoleMapping rm
			JOIN AuthorizationGroup ag
			ON   ag.GroupID = rm.GroupID
			JOIN Role r
			ON   r.RoleID = rm.RoleID
		END
		ELSE
		BEGIN
			INSERT INTO [dbo].[#TopRoles]

			-- Get the mapping of groups to all top level roles including those with no children.
			-- Limit to those found in groupFilter.
			SELECT  ag.GroupID
			, r.RoleID
			, r.ParentRoleID
			FROM RoleMapping rm
			JOIN AuthorizationGroup ag
			ON   ag.GroupID = rm.GroupID
			JOIN ISRM_ftStringToTable(@groupFilter, ',') pn
			ON   ag.GroupName = pn.SplitValue
			JOIN Role r
			ON   r.RoleID = rm.RoleID
		END

		WHILE @@ROWCOUNT > 0
		BEGIN

		INSERT INTO [dbo].[#TopRoles]

			/*Get the mapping of groups to child roles with children. */
			SELECT cr.GroupID
			, r.RoleID
			, r.ParentRoleID
			FROM [dbo].[#TopRoles] cr
			JOIN Role r
			ON   r.ParentRoleID = cr.RoleID  --Gets the children
			AND  not exists (           --Don't return rows that already exist.
			SELECT r2.GroupID 
			FROM #TopRoles r2
			WHERE r2.GroupID = cr.GroupID
			AND   r2.RoleID = r.RoleID)
		END

		WHILE @@ROWCOUNT > 0
		BEGIN

		INSERT INTO [dbo].[#TopRoles]

			/*Get the mapping of groups to child roles with children. */
			SELECT cr.GroupID
			, r.RoleID
			, r.ParentRoleID
			FROM [dbo].[#TopRoles] cr
			JOIN Role r
			ON   r.ParentRoleID = cr.RoleID  --Gets the children
			AND  not exists (           --Don't return rows that already exist.
			SELECT r2.GroupID 
			FROM #TopRoles r2
			WHERE r2.GroupID = cr.GroupID
			AND   r2.RoleID = r.RoleID)
		END

		SELECT 
		ag.GroupName
		, r.RoleName
		FROM [dbo].[#TopRoles] tr
		JOIN Role r
		ON   r.RoleID = tr.RoleID
		JOIN AuthorizationGroup ag
		ON   ag.groupid = tr.GroupID
		LEFT OUTER JOIN Role pr
		ON   pr.RoleID = r.ParentRoleID
		ORDER BY ag.Groupid
		, r.Roleid


	END TRY
BEGIN CATCH
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
           
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH
END

GO


